﻿using FriendStar.Code;
using FriendStar.Domain.Entity.VBaseData;
using FriendStar.Domain.IRepository.VBaseData;
using FriendStar.Repository.VBaseData;
using System.Collections.Generic;
using System.Linq;
using SqlSugarRepository;
using System;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace FriendStar.Application.VBaseData
{
    public class V_CPK_A1_BB_StoProStockCata_RBApp
    {
        private IV_CPK_A1_BB_StoProStockCata_RBRepository service = new V_CPK_A1_BB_StoProStockCata_RBRepository();
        public List<V_CPK_A1_BB_StoProStockCata_RBEntity> GetParameterGridJson(Pagination pagination, string fAccountDate, string fProductionNO,
            string fProductionName, string fProCompanyNO, string FProductionTypeNO)
        {
            var expression = ExtLinq.True<V_CPK_A1_BB_StoProStockCata_RBEntity>();
            if (!string.IsNullOrEmpty(fAccountDate))
            {
                expression = expression.And(t => t.FAccountDate.Contains(fAccountDate));
            }
            if (!string.IsNullOrEmpty(fProductionNO))
            {
                expression = expression.And(t => t.FProductionNO.Contains(fProductionNO));
            }
            if (!string.IsNullOrEmpty(fProductionName))
            {
                expression = expression.And(t => t.FProCompanyName.Contains(fProductionName));
            }
            if (!string.IsNullOrEmpty(fProCompanyNO))
            {
                expression = expression.And(t => t.FProCompanyNO.Contains(fProCompanyNO));
            }
            if (!string.IsNullOrEmpty(FProductionTypeNO))
            {
                expression = expression.And(t => t.FProductionTypeNO.Contains(FProductionTypeNO));
            }
            return service.FindList(expression, pagination).OrderBy(t => t.UseUnit).ToList();
        }

        public List<Dictionary<string, object>> GetParameterGridJsonPrice(Pagination pagination, string fAccountDate, string dwbz, string fProductionNO, string fProductionName, string fProCompanyNO, string FProductionTypeNO, bool isCurrent, string unitcode)
        {
            List<Dictionary<string, object>> result = null;

            int start = 0, end = 0;

            string sql = @"select * from(
            SELECT row_number () over (order by {4}) num,* from {3}) a
            where  num > {0} and num <= {1}  and useUnit = @useUnit {2}", tableName = "V_CPK_44_MX_StoCPKListAcco_JGTZMC";


            StringBuilder conn = null;

            Dictionary<string, object> item = null;

            result = new List<Dictionary<string, object>>();

            conn = new StringBuilder();

            if (!isCurrent)
            {
                tableName = "V_CPK_47_MX_StoCPKListAcco_JGTZMC";
            }

            if (!string.IsNullOrEmpty(fAccountDate))
            {
                conn.Append(string.Format(" and fAccountDate = '{0}'", fAccountDate));
            }

            if (!string.IsNullOrEmpty(dwbz))
            {
                conn.Append(string.Format(" and dwbz = '{0}'", dwbz));
            }

            if (!string.IsNullOrEmpty(fProductionNO))
            {
                conn.Append(string.Format(" and fProductionNO = '{0}'", fProductionNO));
            }

            if (!string.IsNullOrEmpty(fProductionName))
            {
                conn.Append(string.Format(" and fProductionName = '{0}'", fProductionName));
            }

            if (!string.IsNullOrEmpty(fProCompanyNO))
            {
                conn.Append(string.Format(" and fProCompanyNO = '{0}'", fProCompanyNO));
            }

            if (!string.IsNullOrEmpty(FProductionTypeNO))
            {
                conn.Append(string.Format(" and FProductionTypeNO = '{0}'", FProductionTypeNO));
            }

            start = (pagination.page - 1) * pagination.rows;

            end = pagination.page * pagination.rows;

            sql = string.Format(sql, start, end, conn, tableName,Common.BuildSortSQL(pagination.sidx,pagination.sord));

            Common.GetRecords(pagination, tableName, conn);

            SqlParameter[] pars = new SqlParameter[] {
                new SqlParameter("@useUnit", unitcode)
            };
            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.GetConnection(), CommandType.Text, sql, pars))
            {
                while (reader.Read())
                {
                    item = new Dictionary<string, object>();

                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        item.Add(reader.GetName(i), reader[i]);
                    }

                    result.Add(item);
                }
            }

            return result;
        }

        public object GetParameterGridJsonInstoreStatT(Pagination pagination, string fAccountDate, string fHappenComNO, bool isCurrent, string unitcode)
        {
            List<Dictionary<string, object>> result = null;

            int start = 0, end = 0;

            string sql = @"select * from(
            SELECT row_number () over (order by {4}) num,* from {3}) a
            where  num > {0} and num <= {1}  and useUnit = @useUnit {2}", tableName = "V_CPK_44_MX_StoCPKListAcco_RKHZ";


            StringBuilder conn = null;

            Dictionary<string, object> item = null;

            result = new List<Dictionary<string, object>>();

            conn = new StringBuilder();

            if (!isCurrent)
            {
                tableName = "V_CPK_47_MX_StoCPKListAcco_RKHZ";
            }

            if (!string.IsNullOrEmpty(fAccountDate))
            {
                conn.Append(string.Format(" and fAccountDate = '{0}'", fAccountDate));
            }

            if (!string.IsNullOrEmpty(fHappenComNO))
            {
                conn.Append(string.Format(" and FHappenComNO = '{0}'", fHappenComNO));
            }

            start = (pagination.page - 1) * pagination.rows;

            end = pagination.page * pagination.rows;

            sql = string.Format(sql, start, end, conn, tableName,Common.BuildSortSQL(pagination.sidx,pagination.sord));

            Common.GetRecords(pagination, tableName, conn);

            SqlParameter[] pars = new SqlParameter[] {
                new SqlParameter("@useUnit", unitcode)
            };
            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.GetConnection(), CommandType.Text, sql, pars))
            {
                while (reader.Read())
                {
                    item = new Dictionary<string, object>();

                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        item.Add(reader.GetName(i), reader[i]);
                    }

                    result.Add(item);
                }
            }

            return result;
        }

        public object GetParameterGridJsonSalesStatT(Pagination pagination, string fAccountDate, string fHappenComNO, bool isCurrent, string unitcode)
        {
            List<Dictionary<string, object>> result = null;

            int start = 0, end = 0;

            string sql = @"select * from(
            SELECT row_number () over (order by {4}) num,* from {3}) a
            where  num > {0} and num <= {1}  and useUnit = @useUnit {2}", tableName = "V_CPK_44_MX_StoCPKListAcco_CKHZ";


            StringBuilder conn = null;

            Dictionary<string, object> item = null;

            result = new List<Dictionary<string, object>>();

            conn = new StringBuilder();

            if (!isCurrent)
            {
                tableName = "V_CPK_47_MX_StoCPKListAcco_CKHZ";
            }

            if (!string.IsNullOrEmpty(fAccountDate))
            {
                conn.Append(string.Format(" and fAccountDate = '{0}'", fAccountDate));
            }

            if (!string.IsNullOrEmpty(fHappenComNO))
            {
                conn.Append(string.Format(" and FHappenComNO = '{0}'", fHappenComNO));
            }

            start = (pagination.page - 1) * pagination.rows;

            end = pagination.page * pagination.rows;

            sql = string.Format(sql, start, end, conn, tableName,Common.BuildSortSQL(pagination.sidx,pagination.sord));

            Common.GetRecords(pagination, tableName, conn);

            SqlParameter[] pars = new SqlParameter[] {
                new SqlParameter("@useUnit", unitcode)
            };
            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.GetConnection(), CommandType.Text, sql, pars))
            {
                while (reader.Read())
                {
                    item = new Dictionary<string, object>();

                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        item.Add(reader.GetName(i), reader[i]);
                    }

                    result.Add(item);
                }
            }

            return result;
        }

        public object GetParameterGridJsonSalesStat(Pagination pagination, string fAccountDate, string fHappenComNO, string fProductionNO, string fProductionName, string fProCompanyNO, string fProductionTypeNO, bool isCurrent, string unitcode)
        {
            List<Dictionary<string, object>> result = null;

            int start = 0, end = 0;

            string sql = @"select * from(
            SELECT row_number () over (order by {4}) num,* from {3}) a
            where  num > {0} and num <= {1}  and useUnit = @useUnit {2}", tableName = "V_CPK_44_MX_StoCPKListAcco_CKMC";


            StringBuilder conn = null;

            Dictionary<string, object> item = null;

            result = new List<Dictionary<string, object>>();

            conn = new StringBuilder();

            if (!isCurrent)
            {
                tableName = "V_CPK_47_MX_StoCPKListAcco_CKMC";
            }

            if (!string.IsNullOrEmpty(fAccountDate))
            {
                conn.Append(string.Format(" and fAccountDate = '{0}'", fAccountDate));
            }

            if (!string.IsNullOrEmpty(fHappenComNO))
            {
                conn.Append(string.Format(" and FHappenComNO = '{0}'", fHappenComNO));
            }

            if (!string.IsNullOrEmpty(fProductionNO))
            {
                conn.Append(string.Format(" and fProductionNO = '{0}'", fProductionNO));
            }

            if (!string.IsNullOrEmpty(fProductionName))
            {
                conn.Append(string.Format(" and fProductionName = '{0}'", fProductionName));
            }

            if (!string.IsNullOrEmpty(fProCompanyNO))
            {
                conn.Append(string.Format(" and fProCompanyNO = '{0}'", fProCompanyNO));
            }

            if (!string.IsNullOrEmpty(fProductionTypeNO))
            {
                conn.Append(string.Format(" and FProductionTypeNO = '{0}'", fProductionTypeNO));
            }


            start = (pagination.page - 1) * pagination.rows;

            end = pagination.page * pagination.rows;

            sql = string.Format(sql, start, end, conn, tableName,Common.BuildSortSQL(pagination.sidx,pagination.sord));

            Common.GetRecords(pagination, tableName, conn);

            SqlParameter[] pars = new SqlParameter[] {
                new SqlParameter("@useUnit", unitcode)
            };
            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.GetConnection(), CommandType.Text, sql, pars))
            {
                while (reader.Read())
                {
                    item = new Dictionary<string, object>();

                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        item.Add(reader.GetName(i), reader[i]);
                    }

                    result.Add(item);
                }
            }

            return result;
        }

        public object CalcStockCateSum(string date, bool isCurrent, string useunit, string userId)
        {
            int result = 0;

            string sql = @"exec Pro_StoProStockCata_RB_SUM  @useunit,@userId,@date,@current", current = "0";

            try
            {
                if (!isCurrent)
                {
                    current = "1";
                }

                SqlParameter[] pars = new SqlParameter[] {
                    new SqlParameter("@useUnit", useunit),
                    new SqlParameter("@date", date),
                    new SqlParameter("@userId", userId),
                    new SqlParameter("@current", current)
                };

                result = SqlHelper.ExecuteNonQuery(SqlHelper.GetConnection(), CommandType.Text, sql, pars);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                result = -1;
            }

            return result;
        }

        public object GetParameterGridJsonMateQutoCheckDo(int FSign,string useUnit, string curUser)
        {
            int result = 0;


            string sql = @"SELECT FProductionNO,FProductionName,AssembleLine,MakeLine,ProductUnit,ProduWorkshop,FProductionNO_LX,FProductionNO_DE  FROM  V_CLDE_91_JC_AcceCLDEeCheck_HZ WHERE UseUnit =@useUnit AND  ((StartDate < convert(varchar(10),getdate(),120)  AND StopDate > convert(varchar(10),getdate(),120))  OR (StartDate IS NULL))  and {0} is null  ORDER  BY FProductionNO";

            Dictionary<string, object> item = null;

            List<Dictionary<string, object>> list = new List<Dictionary<string, object>>();

            string[] cols = "FProductionNO_LX,FProductionNO_DE".Split(',');

            sql = string.Format(sql,cols[FSign]);

            SqlParameter[] pars = new SqlParameter[] {
                new SqlParameter("@useUnit", useUnit)
            };
            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.GetConnection(), CommandType.Text, sql, pars))
            {
                while (reader.Read())
                {
                    item = new Dictionary<string, object>();

                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        item.Add(reader.GetName(i), reader[i]);
                    }

                    list.Add(item);
                }
            }

            //List<string> codeList = GetProductNoList(FSign);
            ////将不存在于材料检查表里的数据查找出来
            //list = list.FindAll(e => 0 > codeList.IndexOf(e["FProductionNO"].ToString()));

            RemoveMaterialQutoList(FSign);

            string execSql = BuildInsertMateQutoCheckDataSql(list, FSign,useUnit, curUser);

            result = SqlHelper.ExecuteNonQuery(SqlHelper.GetConnSting(), CommandType.Text, execSql);

            return result;
        }

        private List<string> GetProductNoList(int FSign)
        {
            List<string> result = null;

            string[] cols = "FProductionNO,MaterialCode".Split(',');

            string sql = "select {0} from CLDE_91_JC_AcceCLDEeCheck";

            sql = string.Format(sql, cols[FSign]);

            result = new List<string>();

            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.GetConnection(), CommandType.Text, sql))
            {
                while (reader.Read())
                {
                    result.Add(reader[cols[FSign]].ToString());
                }
            }

            return result;
        }

        private int RemoveMaterialQutoList(int FSign)
        {
            int result = 0;

            string sql = string.Empty;

            try
            {
                sql = "delete from CLDE_91_JC_AcceCLDEeCheck where FSign = @FSign";

                SqlParameter par = new SqlParameter("@FSign", FSign);

                result = SqlHelper.ExecuteNonQuery(SqlHelper.GetConnection(), CommandType.Text, sql, par);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                result = -1;
            }

            return result;
        }

        private string BuildInsertMateQutoCheckDataSql(List<Dictionary<string, object>> dataList,int FSign, string useUnit, string curUser)
        {
            StringBuilder sql = null;

            string template = @"insert into CLDE_91_JC_AcceCLDEeCheck(ProblemID, UseUnit, FProductionNO,FProductionName,
                ProductUnit, ProduWorkshop, AssembleLine, MakeLine,  ProblemDes,
                FSign, InputUser, InputDate, InputTime
                ) values('{0}','{1}',{2},'{3}','{4}','{5}','{6}','{7}');
                ", date = string.Empty, time = string.Empty;

            string[] cols = "FProductionNO,FProductionName,ProductUnit,ProduWorkshop,AssembleLine,MakeLine".Split(',');

            List<string> vals = null;

            DateTime now = DateTime.Now;

            sql = new StringBuilder();

            date = now.ToString("yyyy-MM-dd");

            time = now.ToString("HH:mm:ss");
            //FProductionNO,FProductionName,ProductUnit,ProduWorkshop,AssembleLine,MakeLine
            foreach (Dictionary<string, object> item in dataList)
            {
                vals = new List<string>();
                // FProductionNO_LX,FProductionNO_DE
                string[] ProblemDes = "冲压未维护工艺路线,冲压未申报材料定额".Split(',');

                foreach (string col in cols)
                {
                    vals.Add(string.Format("'{0}'", item[col]));

                }

                sql.AppendLine(string.Format(template, Guid.NewGuid(), useUnit, string.Join(",", vals.ToArray()), ProblemDes[FSign], FSign, curUser, date, time));
            }

            return sql.ToString();
        }


        public object GetParameterGridJsonMateQutoCheck(Pagination pagination, string manufactureLine, string produceLine, string productUnit, string produWorkshop, string fProductionNO, string material, string fSign, string useUnit)
        {
            List<Dictionary<string, object>> result = null;

            int start = 0, end = 0;

            string sql = @"select * from(
                select row_number () over (order by fSign) num,* from {3}
                )a
                where  num > {0} and num <= {1}  and useUnit = @useUnit and fSign = @fSign {2} ", tableName = "V_CLDE_91_JC_AcceCLDEeCheck";

            StringBuilder conn = null;

            Dictionary<string, object> item = null;

            result = new List<Dictionary<string, object>>();

            conn = new StringBuilder();


            if (!string.IsNullOrEmpty(produceLine))
            {
                conn.Append(string.Format(" and MakeLine  = '{0}'", produceLine));
            }

            if (!string.IsNullOrEmpty(manufactureLine))
            {
                conn.Append(string.Format(" and AssembleLine = '{0}'", manufactureLine));
            }

            if (!string.IsNullOrEmpty(productUnit))
            {
                conn.Append(string.Format(" and productUnit = '{0}'", productUnit));
            }
            if (!string.IsNullOrEmpty(produWorkshop))
            {
                conn.Append(string.Format(" and produWorkshop = '{0}'", produWorkshop));
            }

            if (!string.IsNullOrEmpty(fProductionNO))
            {
                conn.Append(string.Format(" and fProductionNO = '{0}'", fProductionNO));
            }

            if (!string.IsNullOrEmpty(material))
            {
                conn.Append(string.Format(" and material = '{0}'", material));
            }

            start = (pagination.page - 1) * pagination.rows;

            end = pagination.page * pagination.rows;

            sql = string.Format(sql, start, end, conn, tableName);

            Common.GetRecords(pagination, tableName, conn);

            SqlParameter[] pars = new SqlParameter[] {
                new SqlParameter("@useUnit", useUnit),
                new SqlParameter("@fSign", fSign)
            };
            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.GetConnection(), CommandType.Text, sql, pars))
            {
                while (reader.Read())
                {
                    item = new Dictionary<string, object>();

                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        item.Add(reader.GetName(i), reader[i]);
                    }

                    result.Add(item);
                }
            }

            return result;
        }

        public object GetParameterGridJsonInstockBill(Pagination pagination, string manufactureLine, string startDate, string endDate, string fHappenComNO, string unitcode)
        {
            List<Dictionary<string, object>> result = null;

            int start = 0, end = 0;

            string sql = @"select * from(
                select row_number () over (order by {4}) num,* from {3}
                )a
                where  num > {0} and num <= {1}  and useUnit = @useUnit {2}", tableName = "V_CPK_A6_BB_LJRKRHZ";


            StringBuilder conn = null;

            Dictionary<string, object> item = null;

            result = new List<Dictionary<string, object>>();

            conn = new StringBuilder();


            if (!string.IsNullOrEmpty(manufactureLine))
            {
                conn.Append(string.Format(" and DWBZ = '{0}'", manufactureLine));
            }

            if (!string.IsNullOrEmpty(fHappenComNO))
            {
                conn.Append(string.Format(" and FHappenComNO = '{0}'", fHappenComNO));
            }

            start = (pagination.page - 1) * pagination.rows;

            end = pagination.page * pagination.rows;

            sql = string.Format(sql, start, end, conn, tableName, Common.BuildSortSQL(pagination.sidx,pagination.sord));

            Common.GetRecords(pagination, tableName, conn);

            SqlParameter[] pars = new SqlParameter[] {
                new SqlParameter("@useUnit", unitcode)
            };
            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.GetConnection(), CommandType.Text, sql, pars))
            {
                while (reader.Read())
                {
                    item = new Dictionary<string, object>();

                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        item.Add(reader.GetName(i), reader[i]);
                    }

                    result.Add(item);
                }
            }

            return result;
        }

        public object GetParameterGridJsonOutStockTotal(Pagination pagination, string manufactureLine, string startDate, string endDate, string fHappenComNO, string fProductionTypeNO, string fProCompanyNO, string fProductionName, string fProductionNO, string unitcode)
        {
            List<Dictionary<string, object>> result = null;

            int start = 0, end = 0;

            string sql = @"select * from(
                select row_number () over (order by {4}) num,* from {3}
                )a
                where  num > {0} and num <= {1}  and useUnit = @useUnit {2}", tableName = "V_CPK_A5_BB_LJCKMX";


            StringBuilder conn = null;

            Dictionary<string, object> item = null;

            result = new List<Dictionary<string, object>>();

            conn = new StringBuilder();


            if (!string.IsNullOrEmpty(manufactureLine))
            {
                conn.Append(string.Format(" and DWBZ = '{0}'", manufactureLine));
            }

            if (!string.IsNullOrEmpty(fHappenComNO))
            {
                conn.Append(string.Format(" and FHappenComNO = '{0}'", fHappenComNO));
            }

            if (!string.IsNullOrEmpty(fProductionTypeNO))
            {
                conn.Append(string.Format(" and fProductionTypeNO = '{0}'", fProductionTypeNO));
            }
            if (!string.IsNullOrEmpty(fProCompanyNO))
            {
                conn.Append(string.Format(" and fProCompanyNO = '{0}'", fProCompanyNO));
            }
            if (!string.IsNullOrEmpty(fProductionName))
            {
                conn.Append(string.Format(" and fProductionName = '{0}'", fProductionName));
            }
            if (!string.IsNullOrEmpty(fProductionNO))
            {
                conn.Append(string.Format(" and fProductionNO = '{0}'", fProductionNO));
            }

            start = (pagination.page - 1) * pagination.rows;

            end = pagination.page * pagination.rows;

            sql = string.Format(sql, start, end, conn, tableName,Common.BuildSortSQL(pagination.sidx,pagination.sord));

            Common.GetRecords(pagination, tableName, conn);

            SqlParameter[] pars = new SqlParameter[] {
                new SqlParameter("@useUnit", unitcode)
            };
            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.GetConnection(), CommandType.Text, sql, pars))
            {
                while (reader.Read())
                {
                    item = new Dictionary<string, object>();

                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        item.Add(reader.GetName(i), reader[i]);
                    }

                    result.Add(item);
                }
            }

            return result;
        }

        public object GetParameterGridJsonInStockTotal(Pagination pagination, string manufactureLine, string startDate, string endDate, string fHappenComNO, string fProductionTypeNO, string fProCompanyNO, string fProductionName, string fProductionNO, string unitcode)
        {
            List<Dictionary<string, object>> result = null;

            int start = 0, end = 0;

            string sql = @"select * from(
                select row_number () over (order by {4}) num,* from {3}
                )a
                where  num > {0} and num <= {1}  and useUnit = @useUnit {2}", tableName = "V_CPK_A4_BB_LJRKMX";


            StringBuilder conn = null;

            Dictionary<string, object> item = null;

            result = new List<Dictionary<string, object>>();

            conn = new StringBuilder();


            if (!string.IsNullOrEmpty(manufactureLine))
            {
                conn.Append(string.Format(" and DWBZ = '{0}'", manufactureLine));
            }

            if (!string.IsNullOrEmpty(fHappenComNO))
            {
                conn.Append(string.Format(" and FHappenComNO = '{0}'", fHappenComNO));
            }

            if (!string.IsNullOrEmpty(fProductionTypeNO))
            {
                conn.Append(string.Format(" and fProductionTypeNO = '{0}'", fProductionTypeNO));
            }
            if (!string.IsNullOrEmpty(fProCompanyNO))
            {
                conn.Append(string.Format(" and fProCompanyNO = '{0}'", fProCompanyNO));
            }
            if (!string.IsNullOrEmpty(fProductionName))
            {
                conn.Append(string.Format(" and fProductionName = '{0}'", fProductionName));
            }
            if (!string.IsNullOrEmpty(fProductionNO))
            {
                conn.Append(string.Format(" and fProductionNO = '{0}'", fProductionNO));
            }

            start = (pagination.page - 1) * pagination.rows;

            end = pagination.page * pagination.rows;

            sql = string.Format(sql, start, end, conn, tableName,Common.BuildSortSQL(pagination.sidx,pagination.sord));

            Common.GetRecords(pagination, tableName, conn);

            SqlParameter[] pars = new SqlParameter[] {
                new SqlParameter("@useUnit", unitcode)
            };
            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.GetConnection(), CommandType.Text, sql, pars))
            {
                while (reader.Read())
                {
                    item = new Dictionary<string, object>();

                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        item.Add(reader.GetName(i), reader[i]);
                    }

                    result.Add(item);
                }
            }

            return result;
        }

        public object GetParameterGridJsonOutstockBill(Pagination pagination, string manufactureLine, string startDate, string endDate, string fHappenComNO, string unitcode)
        {
            List<Dictionary<string, object>> result = null;

            int start = 0, end = 0;

            string sql = @"select * from(
                select row_number () over (order by {4}) num,* from {3}
                )a
                where  num > {0} and num <= {1}  and useUnit = @useUnit {2}", tableName = "V_CPK_A7_BB_LJCKRHZ";


            StringBuilder conn = null;

            Dictionary<string, object> item = null;

            result = new List<Dictionary<string, object>>();

            conn = new StringBuilder();


            if (!string.IsNullOrEmpty(manufactureLine))
            {
                conn.Append(string.Format(" and DWBZ = '{0}'", manufactureLine));
            }

            if (!string.IsNullOrEmpty(fHappenComNO))
            {
                conn.Append(string.Format(" and FHappenComNO = '{0}'", fHappenComNO));
            }

            start = (pagination.page - 1) * pagination.rows;

            end = pagination.page * pagination.rows;

            sql = string.Format(sql, start, end, conn, tableName,Common.BuildSortSQL(pagination.sidx,pagination.sord));

            Common.GetRecords(pagination, tableName, conn);

            SqlParameter[] pars = new SqlParameter[] {
                new SqlParameter("@useUnit", unitcode)
            };
            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.GetConnection(), CommandType.Text, sql, pars))
            {
                while (reader.Read())
                {
                    item = new Dictionary<string, object>();

                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        item.Add(reader.GetName(i), reader[i]);
                    }

                    result.Add(item);
                }
            }

            return result;
        }

        public object GetParameterGridJsonBillQueryStat(Pagination pagination, string fDocuNO, string fVoucherNo, string fTypeNO, string FHappenComNO, string startDate, string endDate, string fProductionNO, string fProductionName, string fProCompanyNO, string unitcode)
        {
            List<Dictionary<string, object>> result = null;

            int start = 0, end = 0;

            string sql = @"select * from(
                select row_number () over (order by {4}) num,* from {3}
                )a
                where  num > {0} and num <= {1}  and useUnit = @useUnit {2}", tableName = "V_CPK_44_MX_StoCPKListAcco_Mid_with_da";


            StringBuilder conn = null;

            Dictionary<string, object> item = null;

            result = new List<Dictionary<string, object>>();

            conn = new StringBuilder();


            if (!string.IsNullOrEmpty(fDocuNO))
            {
                conn.Append(string.Format(" and fDocuNO like '%{0}%'", fDocuNO));
            }

            if (!string.IsNullOrEmpty(fVoucherNo))
            {
                conn.Append(string.Format(" and FCredence like '%{0}%'", fVoucherNo));
            }

            if (!string.IsNullOrEmpty(fTypeNO))
            {
                conn.Append(string.Format(" and fTypeNO = '{0}'", fTypeNO));
            }

            if (!string.IsNullOrEmpty(FHappenComNO))
            {
                conn.Append(string.Format(" and FHappenComNO = '{0}'", FHappenComNO));
            }

            if (!string.IsNullOrEmpty(startDate))
            {
                conn.Append(string.Format(" and FCurDate >= '{0}'", startDate));
            }

            if (!string.IsNullOrEmpty(endDate))
            {
                conn.Append(string.Format(" and FCurDate <= '{0}'", endDate));
            }

            if (!string.IsNullOrEmpty(fProductionNO))
            {
                conn.Append(string.Format(" and fProductionNO = '{0}'", fProductionNO));
            }

            if (!string.IsNullOrEmpty(fProductionName))
            {
                conn.Append(string.Format(" and fProductionName = '{0}'", fProductionName));
            }

            if (!string.IsNullOrEmpty(fProCompanyNO))
            {
                conn.Append(string.Format(" and fProCompanyNO = '{0}'", fProCompanyNO));
            }

            start = (pagination.page - 1) * pagination.rows;

            end = pagination.page * pagination.rows;

            sql = string.Format(sql, start, end, conn, tableName, Common.BuildSortSQL(pagination.sidx,pagination.sord));

            Common.GetRecords(pagination, tableName, conn);

            SqlParameter[] pars = new SqlParameter[] {
                new SqlParameter("@useUnit", unitcode)
            };
            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.GetConnection(), CommandType.Text, sql, pars))
            {
                while (reader.Read())
                {
                    item = new Dictionary<string, object>();

                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        item.Add(reader.GetName(i), reader[i]);
                    }

                    result.Add(item);
                }
            }

            return result;
        }

        public List<Dictionary<string, object>> GetParameterGridJsonBatchStoreStat(Pagination pagination, string fDocuNO, string fVoucherNo, string fHappenCompanyNO, string fProductionNO, string fProductionName, string fProCompanyNO, string fProductionTypeNO, string unitcode, string FStoreNO)
        {
            List<Dictionary<string, object>> result = null;

            int start = 0, end = 0;

            string sql = @"select * from(
            SELECT row_number () over (order by {4}) num ,FQuantity_RK-FQuantity_CK FQuantity_RK_FQuantity_CK,* from {3}) a
            where  num > {0} and num <= {1}  and useUnit = @useUnit {2}", tableName = "V_CPK_61_PC_StoCPKAIAOAcco";


            StringBuilder conn = null;

            Dictionary<string, object> item = null;

            result = new List<Dictionary<string, object>>();

            conn = new StringBuilder();


            if (!string.IsNullOrEmpty(fDocuNO))
            {
                conn.Append(string.Format(" and fDocuNO like '%{0}%'", fDocuNO));
            }

            if (!string.IsNullOrEmpty(fVoucherNo))
            {
                conn.Append(string.Format(" and fVoucherNo like '%{0}%'", fVoucherNo));
            }

            if (!string.IsNullOrEmpty(fHappenCompanyNO))
            {
                conn.Append(string.Format(" and fHappenCompanyNO = '{0}'", fHappenCompanyNO));
            }

            if (!string.IsNullOrEmpty(fProductionNO))
            {
                conn.Append(string.Format(" and fProductionNO = '{0}'", fProductionNO));
            }

            if (!string.IsNullOrEmpty(fProductionName))
            {
                conn.Append(string.Format(" and fProductionName = '{0}'", fProductionName));
            }

            if (!string.IsNullOrEmpty(fProCompanyNO))
            {
                conn.Append(string.Format(" and fProCompanyNO = '{0}'", fProCompanyNO));
            }

            if (!string.IsNullOrEmpty(fProductionTypeNO))
            {
                conn.Append(string.Format(" and fProductionTypeNO = '{0}'", fProductionTypeNO));
            }

            if (!string.IsNullOrEmpty(FStoreNO))
            {
                conn.Append(string.Format(" and   FStoreNO = '{0}'", FStoreNO));
            }

            start = (pagination.page - 1) * pagination.rows;

            end = pagination.page * pagination.rows;

            sql = string.Format(sql, start, end, conn, tableName,Common.BuildSortSQL(pagination.sidx,pagination.sord));

            Common.GetRecords(pagination, tableName, conn);

            SqlParameter[] pars = new SqlParameter[] {
                new SqlParameter("@useUnit", unitcode)
            };
            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.GetConnection(), CommandType.Text, sql, pars))
            {
                while (reader.Read())
                {
                    item = new Dictionary<string, object>();

                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        item.Add(reader.GetName(i), reader[i]);
                    }

                    result.Add(item);
                }
            }

            return result;
        }

        public List<Dictionary<string, object>> GetParameterGridJsonInstoreStat(Pagination pagination, string fAccountDate, string FHappenComNO, string fProductionNO, string fProductionName, string fProCompanyNO, string FProductionTypeNO, bool isCurrent, string unitcode)
        {
            List<Dictionary<string, object>> result = null;

            int start = 0, end = 0;

            string sql = @"select * from(
            SELECT row_number () over (order by {4}) num,* from {3}) a
            where  num > {0} and num <= {1}  and useUnit = @useUnit {2}", tableName = "V_CPK_44_MX_StoCPKListAcco_RKMC";


            StringBuilder conn = null;

            Dictionary<string, object> item = null;

            result = new List<Dictionary<string, object>>();

            conn = new StringBuilder();

            if (!isCurrent)
            {
                tableName = "V_CPK_47_MX_StoCPKListAcco_RKMC";
            }

            if (!string.IsNullOrEmpty(fAccountDate))
            {
                conn.Append(string.Format(" and fAccountDate = '{0}'", fAccountDate));
            }

            if (!string.IsNullOrEmpty(FHappenComNO))
            {
                conn.Append(string.Format(" and FHappenComNO = '{0}'", FHappenComNO));
            }

            if (!string.IsNullOrEmpty(fProductionNO))
            {
                conn.Append(string.Format(" and fProductionNO = '{0}'", fProductionNO));
            }

            if (!string.IsNullOrEmpty(fProductionName))
            {
                conn.Append(string.Format(" and fProductionName = '{0}'", fProductionName));
            }

            if (!string.IsNullOrEmpty(fProCompanyNO))
            {
                conn.Append(string.Format(" and fProCompanyNO = '{0}'", fProCompanyNO));
            }

            if (!string.IsNullOrEmpty(FProductionTypeNO))
            {
                conn.Append(string.Format(" and FProductionTypeNO = '{0}'", FProductionTypeNO));
            }


            start = (pagination.page - 1) * pagination.rows;

            end = pagination.page * pagination.rows;

            sql = string.Format(sql, start, end, conn, tableName,Common.BuildSortSQL(pagination.sidx,pagination.sord));

            Common.GetRecords(pagination, tableName, conn);

            SqlParameter[] pars = new SqlParameter[] {
                new SqlParameter("@useUnit", unitcode)
            };
            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.GetConnection(), CommandType.Text, sql, pars))
            {
                while (reader.Read())
                {
                    item = new Dictionary<string, object>();

                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        item.Add(reader.GetName(i), reader[i]);
                    }

                    result.Add(item);
                }
            }

            return result;
        }

        public object GetStatisticLedgersJSONData(Pagination pagination, bool isCurrent, string fProCompanyNO, string fProductionTypeNO, string fProductionNO, string fProductionName, string fAccountDate, string useUnit)
        {
            List<Dictionary<string, object>> result = null;

            int start = 0, end = 0;

            string sql = @"select * from(
            SELECT row_number () over (order by {4}) num,* from {3}) a
            where  num > {0} and num <= {1}  and useUnit = @useUnit {2}", tableName = "V_CPK_71_KC_StoCPKStockCata";

            StringBuilder conn = null;

            Dictionary<string, object> item = null;

            result = new List<Dictionary<string, object>>();

            conn = new StringBuilder();

            if (!isCurrent)
            {
                tableName = "V_CPK_72_KC_StoCPKStockCata_Arc";
            }

            if (!string.IsNullOrEmpty(fAccountDate))
            {
                conn.Append(string.Format(" and fAccountDate = '{0}'", fAccountDate));
            }

            if (!string.IsNullOrEmpty(fProductionTypeNO))
            {
                conn.Append(string.Format(" and fProductionTypeNO = '{0}'", fProductionTypeNO));
            }

            if (!string.IsNullOrEmpty(fProductionNO))
            {
                conn.Append(string.Format(" and fProductionNO = '{0}'", fProductionNO));
            }

            if (!string.IsNullOrEmpty(fProductionName))
            {
                conn.Append(string.Format(" and fProductionName like '%{0}%'", fProductionName));
            }

            if (!string.IsNullOrEmpty(fProCompanyNO))
            {
                conn.Append(string.Format(" and fProCompanyNO = '{0}'", fProCompanyNO));
            }

            start = (pagination.page - 1) * pagination.rows;

            end = pagination.page * pagination.rows;

            sql = string.Format(sql, start, end, conn, tableName,Common.BuildSortSQL(pagination.sidx,pagination.sord));

            Common.GetRecords(pagination, tableName, conn);

            SqlParameter[] pars = new SqlParameter[] {
                new SqlParameter("@useUnit", useUnit)
            };
            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.GetConnection(), CommandType.Text, sql, pars))
            {
                while (reader.Read())
                {
                    item = new Dictionary<string, object>();

                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        item.Add(reader.GetName(i), reader[i]);
                    }

                    item.Add("操作", "<a href=\"javascript:void(0);\" 明细账页>明细账页</a><a href=\"javascript:void(0);\" 统计台账>统计台账</a>");

                    result.Add(item);
                }
            }

            return result;
        }
    }
}
